package com.erp.dao.impl;

import com.erp.bean.ReportForms;
import com.erp.dao.ReportFormDao;
import com.erp.util.db.DataBaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lpy
 * 财务报表数据访问实现类
 */
public class ReportFormDaoImpl extends DataBaseDao implements ReportFormDao {
    @Override
    public int add(ReportForms reportForm) throws SQLException{
        //获取数据库连接对象
        buildConnection();
        String sql = "insert into rforms(oid) values(?)";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1,reportForm.getOid());
        return pstmt.executeUpdate();
    }

    @Override
    public ReportForms findById(int fid) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        ReportForms reportForm = null;
        String sql = "select fid,oid from rforms where fid=?";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setInt(1,fid);
        rs = pstmt.executeQuery();
        if (rs.next()){
            reportForm = new ReportForms();
            reportForm.setFid(rs.getInt(1));
            reportForm.setOid(rs.getString(2));
        }
        return reportForm;
    }

    @Override
    public List<ReportForms> findAll() throws SQLException {
        //获取数据库连接对象
        buildConnection();
        List<ReportForms>  reportForms = new ArrayList<>();
        String sql = "select fid,oid from rforms";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //执行查询 并处理结果
        rs = pstmt.executeQuery();
        while (rs.next()){
            ReportForms reportForm = new ReportForms();
            reportForm.setFid(rs.getInt(1));
            reportForm.setOid(rs.getString(2));
            reportForms.add(reportForm);
        }
        return reportForms;
    }

    @Override
    public List<ReportForms> findAllSpilt(Integer currentPage, Integer pageSize) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        List<ReportForms>  reportForms = new ArrayList<>();
        String sql = "SELECT rforms.fid,orders.oid FROM rforms,orders,commodity WHERE  orders.oid=rforms.oid " +
                     "AND commodity.gid=orders.gid " +
                     "AND orders.gid IN(SELECT gid FROM commodity) limit ?,?";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setInt(1,(currentPage-1)*pageSize);
        pstmt.setInt(2,pageSize);
        //执行查询 并处理结果
        rs = pstmt.executeQuery();
        while (rs.next()){
            ReportForms reportForm = new ReportForms();
            reportForm.setFid(rs.getInt(1));
            reportForm.setOid(rs.getString(2));
            reportForms.add(reportForm);
        }
        return reportForms;
    }

    @Override
    public List<ReportForms> findAllSpilt(Integer currentPage, Integer pageSize, String column, String keyWord) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        List<ReportForms>  reportForms = new ArrayList<>();
        String sql = "SELECT rforms.fid,orders.oid FROM rforms,orders,commodity WHERE  orders.oid=rforms.oid " +
                "AND commodity.gid=orders.gid " +
                "AND orders.gid IN(SELECT gid FROM commodity WHERE "+column+" LIKE ?) limit ?,?";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1,"%"+keyWord+"%");
        pstmt.setInt(2,(currentPage-1)*pageSize);
        pstmt.setInt(3,pageSize);
        //执行查询 并处理结果
        rs = pstmt.executeQuery();
        while (rs.next()){
            ReportForms reportForm = new ReportForms();
            reportForm.setFid(rs.getInt(1));
            reportForm.setOid(rs.getString(2));
            reportForms.add(reportForm);
        }
        return reportForms;
    }

    @Override
    public Long getAllCount() throws SQLException {
        //获取数据库连接对象
        buildConnection();
        Long num = 0L;
        String sql = "select count(*) from rforms";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        if (rs.next()){
            num = rs.getLong(1);
        }
        return num;
    }

    @Override
    public Long getAllCount(String colunm, String keyWord) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        Long num = 0L;
        String sql = "select count(*) from rforms where "+colunm+" like ?";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1,"%"+keyWord+"%");
        rs = pstmt.executeQuery();
        if (rs.next()){
            num = rs.getLong(1);
        }
        return num;
    }
}
